package com.gljx.web.controller;

import com.fasterxml.jackson.annotation.ObjectIdGenerators;
import com.gljx.constant.ActivitiKey;
import com.gljx.util.*;
import com.gljx.web.Entity.*;
import com.gljx.web.service.*;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping("/")
public class CarRefuelController {

    //导入activiti工具
    ActitiviUtil activitiUtil = new ActitiviUtil();
    //自动注入
    @Autowired
    private DepartmentService departmentService;
    //自动注入
    @Autowired
    private CustomLogService customLogService;
    @Autowired
    private CarRefuelService carRefuelService;
    @Autowired
    private CarRefuelEntryService carRefuelEntryService;
    @Autowired
    private CarService carService;
    @Autowired
    private PersonService personService;
    @Autowired
    private OilspecService oilspecService;
    @Autowired
    private TeamService teamService;
    @Autowired
    private CoaceGroupController coaceGroupController;
    //自定义流程工具
    private Axamine axamine = new Axamine();

    /**
     * 查询车辆加油登记
     *
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/queryCarRefuel/look.action")
    public String selCarRefuel(HttpServletRequest request, @RequestParam(value = "fnumber", defaultValue = "") String fnumber
            , @RequestParam(value = "fdescription", defaultValue = "") String fdescription
            , @RequestParam(value = "carNumber", defaultValue = "") String carNumber
            , @RequestParam(value = "beginTime", defaultValue = "") String beginTime
            , @RequestParam(value = "endTime", defaultValue = "") String endTime
            , @RequestParam(value = "type", defaultValue = "") String type
            , @RequestParam(value = "selectState", required = false, defaultValue = "-1") String selectState) throws Exception {
        PageBean<CarRefuel> pb = new PageBean<>();
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        try {
            fnumber = fnumber.trim();//去空格
            fdescription = fdescription.trim();
            carNumber = carNumber.trim();
            beginTime = beginTime.trim();
            endTime = endTime.trim();

            if ("1".equals(selectState)) {//如果传过来的标记为1 所以业务日期为空默认查询全部
                pb = getPageBean(request, user.getFcompanyid(), fnumber, fdescription, carNumber, null, null);
            } else {
                //默认时间
                if ("".equals(beginTime) && "".equals(endTime)) {
                    beginTime = TimeFormat.getMonth();
                    endTime = TimeFormat.getNowTime();
                }
                //获取分页
                pb = getPageBean(request, user.getFcompanyid(), fnumber, fdescription, carNumber, beginTime, endTime);
            }
            //计数器
            int i = 0;
            //缓存需要删除的序列
            List<Integer> tem = new ArrayList<>();
            //获取当前分页的值
            List<CarRefuel> carRefuelList = pb.getBeanList();
            //把获取到的数据传回页面
            if (carNumber.equals("")) {
                carNumber = null;
            }
            for (CarRefuel CarRefuel : carRefuelList) {
                //查询车辆信息
                CarRefuel.setCarRefuelEntryList(carRefuelEntryService.selCarRefuelEntryByParentId(CarRefuel.getFid(), carNumber));
                i++;
                //如果子类为空则记住该序列序号
                if (CarRefuel.getCarRefuelEntryList().size() == 0) {
                    tem.add(i);
                }
            }

            //进行集合删除
            for (int j = 0; j < tem.size(); j++) {
                carRefuelList.remove(tem.get(j) - j - 1);
            }
            //合计信息总条数
            String messageAmount="共有"+pb.getTr()+"条信息";
            //把数据传到页面
            request.setAttribute("messageAmount",messageAmount);
            request.setAttribute("fnumber", fnumber);
            request.setAttribute("fdescription", fdescription);
            request.setAttribute("carNumber", carNumber);
            request.setAttribute("beginTime", beginTime);
            request.setAttribute("endTime", endTime);
            request.setAttribute("carRefuelList", carRefuelList);
            request.setAttribute("pb", pb);
            request.setAttribute("falg", type);
            request.setAttribute("selectState", selectState);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return "backHome/carManagement/carRefuel/carRefuelList";
    }

    /**
     * 跳转到增加驾校车辆加油登记页面
     *
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/intoAddCarRefuel")
    public String intoAddCarRefuel(HttpServletRequest request, String type) throws Exception {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        //获取部门
        Department department = departmentService.queryAdminByFid(user.getFcompanyid());
        //获取最大编号
        /*String number = carRefuelService.selectFnumber(user.getFcompanyid());*/
        //根据上一个编码获取编码
        String fnumber = GetFnumber.fnumbers("JY-");
        //获取当前时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //业务时间
        String time = sdf.format(new Date());
        //获取模式框里面数据
        //获取车辆
        List<Car> cars = carService.queryCar(user.getFcompanyid());
        //获取职员
        //拼接SQL语句
        StringBuffer psql = new StringBuffer();
        psql.append("select T.FID, T.FNUMBER,to_char(t.CFBIRTHDAY,'YYYY-MM-DD ') cfbirthdays,to_char(t.cfstartdate,'YYYY-MM-DD ') cfstartdate,");
        psql.append("to_char(t.cfenddate,'YYYY-MM-DD ') cfenddate,to_char(t.cfbegindate,'YYYY-MM-DD ') cfbegindate,to_char(t.CFHTDQSJ,'YYYY-MM-DD ') cfhtdqsj,");
        psql.append("T.CFIDCARDNO, T.CFCELL, T.CFADDRESS,T.CFPERSONTYPE, T.CFSEX,T.CFXCTZ, T.CFGWYD, T.CFQJJL, T.CFWJJLJL,p.fname_l2 as positiongradeName,t.cfaccountsno, ");
        psql.append("T.CFSHJLJL,T.FNAME_L2,T.CFZSTC,T.CFPXTC,A.FNAME_L2 AS CFPERSONDEPNAME,C.fnumber AS CFCARNAME from ct_ds_person t left join ");
        psql.append("CT_DS_ADMIN A ON A.FID=T.CFPERSONDEPID left join CT_DS_CAR C ON C.FID=T.CFCARID left join CT_DS_POSITIONGRADE p on t.cfjobgradeid=p.fid ");
        List<Person> persons = personService.selectPerson(psql.toString());
        //获取其汽油规格
        List<Oilspec> oilspecs = oilspecService.queryOilsperAll();
        //获取教练组
        //查询教练组信息
        Map map2 = coaceGroupController.getPageCoaceGroup(request, "", "", "0");
        //获取教练队
        List<Team> teams = teamService.queryTeamByFcontrolunitid(user.getFcompanyid());
        /*获取当前时间*/
        String createTime = TimeFormat.timeFormat(new Date());
        //把数据返回到页面中
        request.setAttribute("persons", persons);
        request.setAttribute("cars", cars);
        request.setAttribute("oilspecs", oilspecs);
        request.setAttribute("pb2", map2.get("pb"));
        request.setAttribute("coacegroups", map2.get("sList"));
        request.setAttribute("department", department);
        request.setAttribute("teams", teams);
        request.setAttribute("fnumber", fnumber);
        request.setAttribute("time", time);
        request.setAttribute("type", type);
        return "backHome/carManagement/carRefuel/addCarRefuel";
    }

    /**
     * 增加驾校车辆加油登记信息
     *
     * @param request
     * @param carRefuel
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/addCarRefuel")
    public String addCarRefuel(HttpServletRequest request, CarRefuel carRefuel) throws Exception {
        String[] carId = request.getParameterValues("carId");//车辆id
        String[] fseq = request.getParameterValues("fseq");//序号
        String[] cfcartype = request.getParameterValues("cfcartype");//车型
        String[] cftrainid = request.getParameterValues("cftrainid");//教练id
        String[] cfteamid = request.getParameterValues("cfteamid");//教练队id
        for (int i = 0; i < cfteamid.length; i++) {
            if ("".equals(cfteamid[i])) {
                cfteamid[i] = null;
            }
        }
        String[] cfgroupid = request.getParameterValues("cfgroupid");//教练组id
        for (int i = 0; i < cfgroupid.length; i++) {
            if ("".equals(cfgroupid[i])) {
                cfgroupid[i] = "0";
            }
        }
        String[] cfkemu = request.getParameterValues("cfkemu");//科目
        for (int i = 0; i < cfkemu.length; i++) {
            if ("".equals(cfkemu[i])) {
                cfkemu[i] = "0";
            }
        }
        String[] cfqty = request.getParameterValues("cfqty");//数量
        for (int i = 0; i < cfqty.length; i++) {
            if ("".equals(cfqty[i])) {
                cfqty[i] = "0";
            }
        }
        String[] cfoilspecid = request.getParameterValues("cfoilspecid");//汽油规格id
        String[] cfdesc = request.getParameterValues("cfdesc");//备注
        String[] cfdepname = request.getParameterValues("cfdepname");//部门
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        //利用UUID生成fid
        carRefuel.setFid(GetFid.UUIDIntoFid());
        //公司id
        carRefuel.setCfficompanyid(user.getFcompanyid());
        //控制单元id
        carRefuel.setFcontrolunitid(user.getFcompanyid());
        //创建人id
        carRefuel.setFcreatorid(user.getFid());
        //获取当前时间
        String createTime = TimeFormat.timeFormat(new Date());
        carRefuel.setFcreatetime(createTime);
        //添加
        int row = carRefuelService.addCarRefuel(carRefuel, carId, fseq, cfcartype,
                cftrainid, cfteamid, cfgroupid, cfkemu, cfqty, cfoilspecid, cfdesc, cfdepname);
        String result = "";
        if (row > 0) {
            result = "1";
        }
        //添加日志
        customLogService.addLog("车辆管理", TimeFormat.timeFormat(new Date()) + "驾校车辆加油登记进行新增", null, user.getFid());
        return "redirect:/DrivingSys/intoAddCarRefuel?type="+result;
    }

    /**
     * 根据fid删除驾校车辆加油登记信息
     *
     * @param request
     * @param deleteId
     * @param fid
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/deleteCarRefuelByFid")
    public String deleteCarRefuelByFid(HttpServletRequest request, String[] deleteId, String fid) throws Exception {
        User user = (User) request.getSession().getAttribute("user");
        //删除记录
        if (fid == null || fid.equals("")) {
            for (int i = 0; i < deleteId.length; i++) {
                /*过滤为空的删除操作*/
                if (!deleteId[i].equals("") && deleteId[i] != null) {
                    //根据fid查找单据编号
                    CarRefuel carRefuel = carRefuelService.selCarRefuelByFid(deleteId[i]);
                    //删除历史流程或者进行中的流程
                    activitiUtil.deleteTask(ActivitiKey.PUBLIC_KEY, carRefuel.getFnumber());
                    carRefuelService.deleteCarRefuelByFid(deleteId[i]);//删除父类
                    carRefuelEntryService.deleteByParentId(deleteId[i]);//删除子类
                }
            }
        } else {
            //根据fid查找单据编号
            CarRefuel carRefuel = carRefuelService.selCarRefuelByFid(fid);
            //删除历史流程或者进行中的流程
            activitiUtil.deleteTask(ActivitiKey.PUBLIC_KEY, carRefuel.getFnumber());
            carRefuelService.deleteCarRefuelByFid(fid);//删除父类
            carRefuelEntryService.deleteByParentId(fid);//删除子类
        }
        //添加日志
        customLogService.addLog("车辆管理", TimeFormat.timeFormat(new Date()) + "驾校车辆加油登记进行删除", null, user.getFid());
        return "redirect:/DrivingSys/queryCarRefuel/look.action";
    }

    /**
     * 根据fid查找或者修改驾校车辆加油登记
     *
     * @param request
     * @param fid
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/intoUpdateCarRefuel")
    public String intoUpdateCarRefuel(HttpServletRequest request, String fid) throws Exception {
        // 获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        // 获取部门
        Department department = departmentService.queryAdminByFid(user.getFcompanyid());
        // 获取当前时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        // 获取模式框里面数据
        // 获取车辆
        List<Car> cars = carService.queryCar(user.getFcompanyid());
        // 获取职员
        // 拼接SQL语句
        StringBuffer psql = new StringBuffer();
        psql.append(
                "select T.FID, T.FNUMBER,to_char(t.CFBIRTHDAY,'YYYY-MM-DD ') cfbirthdays,to_char(t.cfstartdate,'YYYY-MM-DD ') cfstartdate,");
        psql.append(
                "to_char(t.cfenddate,'YYYY-MM-DD ') cfenddate,to_char(t.cfbegindate,'YYYY-MM-DD ') cfbegindate,to_char(t.CFHTDQSJ,'YYYY-MM-DD ') cfhtdqsj,");
        psql.append(
                "T.CFIDCARDNO, T.CFCELL, T.CFADDRESS,T.CFPERSONTYPE, T.CFSEX,T.CFXCTZ, T.CFGWYD, T.CFQJJL, T.CFWJJLJL,p.fname_l2 as positiongradeName,t.cfaccountsno, ");
        psql.append(
                "T.CFSHJLJL,T.FNAME_L2,T.CFZSTC,T.CFPXTC,A.FNAME_L2 AS CFPERSONDEPNAME,C.fnumber AS CFCARNAME from ct_ds_person t left join ");
        psql.append(
                "CT_DS_ADMIN A ON A.FID=T.CFPERSONDEPID left join CT_DS_CAR C ON C.FID=T.CFCARID left join CT_DS_POSITIONGRADE p on t.cfjobgradeid=p.fid ");
        List<Person> persons = personService.selectPerson(psql.toString());
        // 获取其汽油规格
        List<Oilspec> oilspecs = oilspecService.queryOilsperAll();
        // 获取教练组
        // 查询教练组信息
        Map map2 = coaceGroupController.getPageCoaceGroup(request, "", "", "0");
        // 获取教练队
        List<Team> teams = teamService.queryTeamByFcontrolunitid(user.getFcompanyid());
        //获取需要查找或者修改的驾校车辆加油登记信息
        CarRefuel carRefuel = carRefuelService.selCarRefuelByFid(fid);
        //获取子类
        carRefuel.setCarRefuelEntryList(carRefuelEntryService.selCarRefuelEntryByParentId(carRefuel.getFid(), null));
        // 把数据返回到页面中
        request.setAttribute("carRefuel", carRefuel);
        request.setAttribute("persons", persons);
        request.setAttribute("cars", cars);
        request.setAttribute("oilspecs", oilspecs);
        request.setAttribute("pb2", map2.get("pb"));
        request.setAttribute("coacegroups", map2.get("sList"));
        request.setAttribute("department", department);
        request.setAttribute("teams", teams);
        return "backHome/carManagement/carRefuel/updateCarRefuel";
    }

    /**
     * 修改
     *
     * @param request
     * @param carRefuel
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/updateCarRefuel")
    public String updateCarRefuel(HttpServletRequest request, CarRefuel carRefuel) throws Exception {
        String[] carId = request.getParameterValues("carId");//车辆id
        String[] fseq = request.getParameterValues("fseq");//序号
        String[] cfcartype = request.getParameterValues("cfcartype");//车型
        String[] cftrainid = request.getParameterValues("cftrainid");//教练id
        String[] cfteamid = request.getParameterValues("cfteamid");//教练队id
        for (int i = 0; i < cfteamid.length; i++) {
            if ("".equals(cfteamid[i])) {
                cfteamid[i] = null;
            }
        }
        String[] cfgroupid = request.getParameterValues("cfgroupid");//教练组id
		/*for (int i = 0; i <cfgroupid.length ; i++) {
			if(cfgroupid[i] != null){
				if("".equals(cfgroupid[i])){
					cfgroupid = null;
				}
			}
		}*/
        String[] cfkemu = request.getParameterValues("cfkemu");//科目
        for (int i = 0; i < cfkemu.length; i++) {
            if (cfkemu[i].equals("")) {
                cfkemu[i] = "0";
            }
        }
        String[] cfqty = request.getParameterValues("cfqty");//数量
        for (int i = 0; i < cfqty.length; i++) {
            if ("".equals(cfqty[i])) {
                cfqty[i] = "0";
            }
        }
        for(int i=0;i<carId.length;i++){
            fseq[i]=(i+1)+"";
        }
        String[] cfoilspecid = request.getParameterValues("cfoilspecid");//汽油规格id
        String[] cfdesc = request.getParameterValues("cfdesc");//备注
        String[] cfdepname = request.getParameterValues("cfdepname");//部门
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");

        //修改人id
        carRefuel.setFlastupdateuserid(user.getFid());
        //获取当前时间
        String updateTime = TimeFormat.timeFormat(new Date());
        carRefuel.setFlastupdatetime(updateTime);
	  	/*if(carRefuel.getCfbillstate() == 5L){//审核
	  		carRefuel.setFauditorid(user.getFid());
	  		carRefuelService.updateCarRefuelByFid(carRefuel);
	  	}else{*/
        int row = carRefuelService.updateCarRefuel(carRefuel, carId, fseq, cfcartype,
                cftrainid, cfteamid, cfgroupid, cfkemu, cfqty, cfoilspecid, cfdesc, cfdepname);
        String result = "";
        if (row > 0) {
            result = "1";
        }
        //}
        //添加日志
        customLogService.addLog("车辆管理", TimeFormat.timeFormat(new Date()) + "驾校车辆加油登记进行修改", null, user.getFid());
        return "redirect:/DrivingSys/queryCarRefuel/look.action?type=" + result;
    }

    /**
     * 根据名称查找教练队信息
     *
     * @param name
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/queryTeamByName2")
    @ResponseBody
    public List<Team> selTeamByName(HttpServletRequest request, String name) throws Exception {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        List<Team> teams = new ArrayList<>();
        name = name.trim();
        if ("".equals(name) || name == null) {
            teams = teamService.queryTeamByFcontrolunitid(user.getFcompanyid());//如果为空就调用查询的方法
        } else {
            // 查询数据
            teams = teamService.queryTeamByFcontrolunitidAndFnameL2(user.getFcompanyid(), name);
        }
        return teams;
    }

    /**
     * 提交单据
     *
     * @param fid
     * @param request
     * @param fnumber
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "submitCarRefuelActiviti")
    @ResponseBody
    public Object submitCarRefuelActiviti(String fid, HttpServletRequest request, String fnumber) throws Exception {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        CarRefuel carRefuel = new CarRefuel();
        carRefuel.setFid(fid);
        carRefuel.setCfbillstate(4L);
        carRefuel.setFlastupdateuserid(user.getFid());
        //创建流程
        axamine.submissionProcess(fnumber);
        int row = carRefuelService.updateCarRefuelByFid(carRefuel);
        String result = "failed";
        if (row > 0) {
            result = "success";
        }

        return result;
    }

    /**
     * 审核单据
     *
     * @param request
     * @param fid
     * @param agree
     * @param auditcomment
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "checkCarRefuelActiviti")
    @ResponseBody
    public Object checkCarRefuelActiviti(HttpServletRequest request, String fnumber, String fid, String agree, String auditcomment) throws Exception {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        CarRefuel carRefuel = new CarRefuel();
        //获取当前修改时间
        String updateTime = TimeFormat.timeFormat(new Date());
        String result = "failed";
        int row1 = 0;
        if (agree.equals("1")) {//同意
            ServerResponse serverResponse = axamine.auditProcess(fnumber, agree, auditcomment);
            if (serverResponse.getStatus() == 1) {//结束
                carRefuel.setFid(fid);
                carRefuel.setCfbillstate(5L);
                carRefuel.setFlastupdateuserid(user.getFid());
                carRefuel.setFauditorid(user.getFid());
                row1 = carRefuelService.updateCarRefuelByFid(carRefuel);
                if (row1 > 0) {//修改单据
                    result = "success";
                }
            } else if (serverResponse.getStatus() == 2) {
                result = "success";
            } else {

            }
        } else {
            ServerResponse serverResponse = axamine.auditProcess(fnumber, agree, auditcomment);
            if (serverResponse.getStatus() == 2) {
                carRefuel.setFid(fid);
                carRefuel.setCfbillstate(3L);
                carRefuel.setFlastupdateuserid(user.getFid());
                carRefuel.setFauditorid(user.getFid());
                //修改父类
                row1 = carRefuelService.updateCarRefuelByFid(carRefuel);
                if (row1 > 0) {
                    result = "success";
                }
            }
        }
        return result;
    }

    /**
     * 反审核单据
     *
     * @param request
     * @param fid
     * @param againstAuditcomment
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "againstCheckCarRefuelActiviti")
    @ResponseBody
    public Object againstCarRefuelActiviti(HttpServletRequest request, String fid, String fnumber, String againstAuditcomment) throws Exception {
        //获取当前登录用户的信息
        User user = (User) request.getSession().getAttribute("user");
        String result = "falied";
        //将单据状态修改成保存状态3
        CarRefuel carRefuel = new CarRefuel();
        carRefuel.setFid(fid);
        carRefuel.setCfbillstate(3L);//保存状态
        carRefuel.setFauditorid(user.getFid());//设置反审计人id
        int row = carRefuelService.updateCarRefuelByFid(carRefuel);
        axamine.againstAuditProcess(fnumber, againstAuditcomment);
        if (row > 0) {
            result = "success";
        }
        return result;
    }

    @SuppressWarnings("unchecked")
    /**
     * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
     * @return 返回PageBean实体类
     * @throws Exception
     */
    public PageBean<CarRefuel> getPageBean(HttpServletRequest request, String companyId, String fnumber,
                                           String fdescription, String carNumber, String beginTime, String endTime) throws Exception {

        int offset;//从第几条数据开始加载
        if (request.getParameter("pager.offset") == null) {
            offset = 0;
        } else {
            offset = Integer.parseInt(request.getParameter("pager.offset"));
        }
        //拼接SQL语句
        StringBuffer psql = new StringBuffer();
        psql.append(" SELECT DISTINCT C.FCREATORID, to_char(C.FCREATETIME,'YYYY-MM-DD HH24:mi:ss') FCREATETIME,C.FLASTUPDATEUSERID, "
                + "to_char(C. FLASTUPDATETIME,'YYYY-MM-DD HH24:mi:ss') FLASTUPDATETIME,C.FCONTROLUNITID,C.FNUMBER,to_char(C.FBIZDATE,'YYYY-MM-DD') FBIZDATE,C.FHANDLERID,"
                + " C.FDESCRIPTION,C.FHASEFFECTED,C.FAUDITORID,C.FSOURCEBILLID,C.FSOURCEFUNCTION,C.FID,C.FFIVOUCHERED,C.CFFICOMPANYID,C.CFJYPERSONID,C.CFBILLSTATE,"
                + " E.NAME AS checkName,U.NAME AS createName, P.FNAME_L2 as cfjypersonName "
                + "FROM CT_DS_CARREFUEL C LEFT JOIN T_E_USER U ON U.FID=C.FCREATORID LEFT JOIN T_E_USER E ON E.FID=C.FAUDITORID "
                + " LEFT JOIN CT_DS_CARREFUELENTRY C1 ON C1.FPARENTID=C.FID"
                + " LEFT JOIN CT_DS_CAR C2 ON C2.FID=C1.CFCARNOID "
                + "LEFT JOIN CT_DS_PERSON P ON P.FID =  C.CFJYPERSONID"
                + " WHERE C.FCONTROLUNITID='" + companyId + "'");
        if (fnumber == null || fnumber.equals("")) {

        } else {
            psql.append("AND C.FNUMBER LIKE '%" + fnumber + "%' ");
        }
        if (fdescription == null || fdescription.equals("")) {

        } else {
            psql.append("AND C.FDESCRIPTION LIKE '%" + fdescription + "%'");
        }
        if (carNumber == null || "".equals(carNumber)) {

        } else {
            psql.append(" AND C2.FNUMBER LIKE '%" + carNumber + "%' ");
        }
        if (beginTime == null || endTime == null) {
            //beginTime = TimeFormat.getMonth();
            /*} else if (endTime == null || endTime.equals("")) {*/
			/*SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			endTime = sdf.format(new Date());
			psql.append("AND TO_CHAR(C.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'");*/
        } else {
            psql.append("AND TO_CHAR(C.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'");
        }
        psql.append(" ORDER BY to_char(C.FBIZDATE,'YYYY-MM-DD') DESC");
        //System.out.println(psql);
        int psize = 50; //每页显示数
        int pindex = offset / psize;//+1  页码从0开始
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = carRefuelService.selectPage(pindex, psql.toString(), psize);
        PageBean<CarRefuel> pb = new PageBean<>();
        pb.setPc(pindex); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql.toString()); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        pb.setBeanList((List<CarRefuel>) paramMap.get("v_cur")); //每页数据集
        return pb;
    }


    @RequestMapping(value = "/RefuelRegisterImportExcel", method = RequestMethod.POST)
    @ResponseBody
    public Object RefuelRegisterImportExcel(@RequestParam(value = "file", required = false) MultipartFile file,
                                         HttpServletRequest request, HttpServletResponse response) throws Exception {

        ImportExcelUtil_MrWu importExcel_MrWu = new ImportExcelUtil_MrWu();
        /*CommonsMultipartFile cf = (CommonsMultipartFile) file;
        DiskFileItem fi = (DiskFileItem) cf.getFileItem();
        File f = fi.getStoreLocation();*/

        String fileName = file.getOriginalFilename();
        // 获取文件后缀
        String prefix=fileName.substring(fileName.lastIndexOf("."));
        // 用uuid作为文件名，防止生成的临时文件重复
        final File excelFile = File.createTempFile(GetFid.UUIDIntoFid(), prefix);
        // MultipartFile to File
        file.transferTo(excelFile);

        List<List<Object>> list = importExcel_MrWu.importExcel(excelFile);

        //程序结束时，删除临时文件
        deleteFile(excelFile);

        return carRefuelService.RefuelRegisterImportExcel(list,fileName);
    }


    private void deleteFile(File... files) {
        for (File file : files) {
            if (file.exists()) {
                file.delete();
            }
        }
    }

    @RequestMapping("/exportDownLoadExcel")
    @ResponseBody
    public void downLoadExcel(HttpServletRequest request,
                              HttpServletResponse response,String state) throws Exception {
        String download = request.getSession().getServletContext().getRealPath("/xls/"); //获取下载路劲

        if ("qy".equals(state)){
            ////依次传入需要下载的文件名，文件格式，路径，response参数
            DownloadXls.downLoadFile(DownloadXls.DOWNLOADNAME_QY,DownloadXls.DOWNLOAD_TYPE_XLS,download, response);

        }else if ("cy".equals(state)){
            DownloadXls.downLoadFile(DownloadXls.DOWNLOADNAME_CY,DownloadXls.DOWNLOAD_TYPE_XLS,download, response);

        }

    }

    /**
     * 取记录总数 dmz 20160921
     *
     * @param psql SQL语句
     * @return Map
     * @throws Exception
     */
    public Map<String, Object> getPageBeanCount(String psql) throws Exception {

        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = carRefuelService.selectPageCount(psql);
        return paramMap;
    }


    /**
     * 驾校车辆加油登记 导出excel
     *
     * @param response
     * @param request
     * @param fnumber
     * @param fdescription
     * @param carNumber
     * @param beginTime
     * @param endTime
     */
    @RequestMapping(value = "/importCarRefuel")
    public void importCarRefuel(HttpServletResponse response, HttpServletRequest request
            , @RequestParam(value = "fnumber", defaultValue = "") String fnumber
            , @RequestParam(value = "fdescription", defaultValue = "") String fdescription
            , @RequestParam(value = "carNumber", defaultValue = "") String carNumber
            , @RequestParam(value = "beginTime", defaultValue = "") String beginTime
            , @RequestParam(value = "endTime", defaultValue = "") String endTime) {
        if ("".equals(carNumber)) {
            carNumber = null;
        }
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        //定义接收类型
        List<Object> data = new ArrayList<>();
        //查询数据
        List<CarRefuel> carRefuels = carRefuelService.selectCarRefuel(user.getFcompanyid(), fnumber, fdescription, carNumber, beginTime, endTime);
        for (CarRefuel carRefuel : carRefuels) {
            carRefuel.setCarRefuelEntryList(carRefuelEntryService.selCarRefuelEntryByParentId(carRefuel.getFid(), carNumber));
        }
        for (CarRefuel carRefuel : carRefuels) {
            for (int i = 0; i < carRefuel.getCarRefuelEntryList().size(); i++) {
                data.add(carRefuel.getFnumber());
                data.add(carRefuel.getFbizdate());
                data.add(carRefuel.getFdescription());
                data.add(carRefuel.getCheckName());
                data.add(carRefuel.getCreateName());
                if (carRefuel.getCfbillstate() == 3L) {
                    data.add("保存");
                } else if (carRefuel.getCfbillstate() == 4L) {
                    data.add("提交");
                } else {
                    data.add("审核");
                }
                data.add(carRefuel.getCarRefuelEntryList().get(i).getCftrainName());
                data.add(carRefuel.getCarRefuelEntryList().get(i).getCarFnumber());
                data.add(carRefuel.getCarRefuelEntryList().get(i).getCfcartype());
                if (carRefuel.getCarRefuelEntryList().get(i).getCfkemu() == 1L) {
                    data.add("科目一");
                } else if (carRefuel.getCarRefuelEntryList().get(i).getCfkemu() == 2L) {
                    data.add("科目二");
                } else if (carRefuel.getCarRefuelEntryList().get(i).getCfkemu() == 3L) {
                    data.add("科目三");
                } else if (carRefuel.getCarRefuelEntryList().get(i).getCfkemu() == 4L) {
                    data.add("备用");
                } else {
                    data.add("");
                }
                data.add(carRefuel.getCarRefuelEntryList().get(i).getCfqty());
                data.add(carRefuel.getCfjypersonName());
                data.add(carRefuel.getCarRefuelEntryList().get(i).getCfoilspecName());
                data.add(carRefuel.getCarRefuelEntryList().get(i).getCfdesc());
                data.add(carRefuel.getCarRefuelEntryList().get(i).getCfgroupName());
                data.add(carRefuel.getCarRefuelEntryList().get(i).getCfteamName());
                data.add(carRefuel.getCarRefuelEntryList().get(i).getCfdepname());
            }
        }
        //头部数据
        String head[] = {"单据编号", "业务日期", "参考信息", "审核人名称", "制单人名称", "单据状态", "教练员", "车号", "车型", "科目", "加油量(公升)", "加油员", "油规格", "备注", "教练组", "教练队", "部门"};
        //长度
        Integer length[] = {9000, 5000, 5000, 3000, 3000, 3000, 3000, 3000, 3000, 3000, 3000, 3000, 5000, 3000, 3000, 3000, 3000};
        try {
            //导出Excel
            CommonExport.importExcel(response, "驾校车辆加油登记" + TimeFormat.getNowTime2() + ".xls", "驾校车辆加油登记", data, head, length);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 根据车辆名称查询车辆历史信息
     * @return
     */
    @ResponseBody
    @RequestMapping (value = "/selCarRefuelByName")
    public CarRefuelEntry selCarAddOilByName(String carName, HttpServletRequest request) {
       User user=  (User) request.getSession().getAttribute("user");
        CarRefuelEntry carRefuelEntry = new CarRefuelEntry();

        List<CarRefuelEntry> list = carRefuelService.selCarRefuelEntryBycarName(carName);//查询的驾校加油的登记内容
        if(!list.isEmpty()) {
            carRefuelEntry = list.get(0);
            if(carRefuelEntry.getCfdepname()==null){
               List<Car> carList=carService.selectCarExcel(carRefuelEntry.getCarFnumber(),user.getFcompanyid());//查询部门编号
                carRefuelEntry.setCfdepname(carList.get(0).getDeptName());
            }
        }else{
            /** 当list为null的时候，即selCarRefuelEntryBycarName的查询方法出现问题的时候
             *  再通过carService查询,如果查询也为null,即该车辆不存在*/
            List<Car> carList=carService.queryCarByFnumber(carName,user.getFcompanyid());
            if(carList!=null){
            carRefuelEntry.setCarFnumber((String)carList.get(0).getFnumber());//添加车牌号码
            carRefuelEntry.setCfdepname(carList.get(0).getDeptName());//添加部门编号
            carRefuelEntry.setCfcarnoid(carList.get(0).getFid());//添加车辆id
            carRefuelEntry.setCfcartype(carList.get(0).getFnameL2());//添加车型
            list.add(carRefuelEntry);
            }else {
                  carRefuelEntry.setCarFnumber("该车辆不存在");//如果车辆也无法搜索到的话，证明该车辆没有登记到
            }
        }
        return carRefuelEntry;
    }
}